SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE    PROCEDURE [dbo].[SP_TCMOVI_CAJA_I03]  
/*-------------------------------------------------------*/  
/*---Empresa              : OFISIS S.A.                --*/  
/*---Cliente              : OFISIS                 --*/  
/*---Sistema              : Tesoreria                  --*/  
/*---M¸dulo               : Empresa                    --*/  
/*---Programa             : Transferencia entre caja   --*/  
/*---                       en forma masiva            --*/  
/*---Script               : tcmovi_b.sql               --*/  
/*---Nombre SP            : SP_TCMOVI_CAJA_I03         --*/  
/*---Desarrollado por     : Javier Flores A.           --*/  
/*---Fecha Creaci¸n       : 24/06/1999                 --*/  
/*---Base Datos           : Microsoft Sql Server       --*/  
/*---Versi¸n              : 7.0                        --*/  
/*---Invoca a SP          :                            --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Modificado 1 por     : Carlos R Marquez E         --*/  
/*---Fecha Modificaci¸n   : 09/11/2005                 --*/   
/*---Detalle Modificaci¸n : Error Insercion Tipos Dat. --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Comentarios                                       --*/  
/*---                                                  --*/  
/*---                                                  --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Drop Proc SP_TCMOVI_CAJA_I03                      --*/  
/*---Grant all on SP_TCMOVI_CAJA_I03 to public         --*/   
/*--- SP_TCMOVI_CAJA_I03  '01',  'EGOME',  'CAJAMN',  '2003',  '11',  'E',  '00200066',  NULL,  
 '11/30/2003',  'SOL',  174,  50,  '01',  'CAJAME',  '00000003',  '999',  3.48,  NULL,  'G',  0,  2,  '001'  
--*/  
/*-------------------------------------------------------*/  
 @ISCO_EMPR   TD_VC_002,   
 @ISCO_USUA_MODI  TD_VC_008_USUA,  
 @ISCO_CAJA   TD_VC_008,   
 @ISAA_CAJA   TD_VC_004,   
 @ISMM_CAJA   TD_VC_002,   
 @ISTI_MOVI   TD_VC_001,    
 @ISNU_COMP_CAJA  TD_VC_008,   
 @INNU_SECU   TD_IN_001,  
 @IDFE_OPER   TD_DT_001,   
 @ISCO_MONE   TD_VC_003,   
 @INIM_MOVI   TD_NU_016_004,   
 @INIM_CONV   TD_NU_016_004,  
 @ISCO_EMPR_DEST  TD_VC_002,  
 @ISCO_CAJA_DEST  TD_VC_008,   
 @ISNU_COMP_DEST  TD_VC_008,  
 @ISCO_TIPO_DEST  TD_VC_003,   
 @INFA_TIPO_DEST  TD_NU_015_006,   
 @ISDE_OBSE   TD_VC_100,  
 @ISTI_CORR_INEG  TD_VC_001,  
 @INUL_CORR_INEG  TD_IN_001,   
 @INUL_CORR_INGR  TD_IN_001,  
 @ISCO_UNID_CNTB  TD_VC_003
	As
Declare @VNCO_NUEV_NUME TD_IN_001,  @VSCO_NUEV_CHAR TD_VC_008,  
         @VSAA_CAJA_DEST TD_VC_004,  @VSMM_CAJA_DEST TD_VC_002,    
         @VNNU_REGI      TD_IN_001,  @VNNU_SECU TD_IN_001, @VNIM_CONV TD_NU_016_004,  
         @VSCO_MONE      TD_VC_003,  @VNIM_TOTA TD_NU_016_004, @VNIM_MOVI_ANTE  TD_NU_016_004,  
         @VNUL_CORR_INGR TD_IN_001,  @VNUL_CORR_INEG TD_IN_001  
  
 BEGIN TRAN SP_TCMOVI_CAJA_I03  
  
  Select @VSAA_CAJA_DEST = Convert(Char(4),Datepart(YY, @IDFE_OPER))  
  Select @VSMM_CAJA_DEST = Convert(Char(2),Datepart(MM, @IDFE_OPER))  
  
  If Datalength (rtrim(@VSMM_CAJA_DEST)) = 1  
     Select @VSMM_CAJA_DEST = '0' + @VSMM_CAJA_DEST   
  
  If @INNU_SECU IS NULL  
     Begin  
       Select @VNNU_SECU = NU_SECU  
       From   TDMOVI_TRCA  
       Where  CO_EMPR = @ISCO_EMPR  
       and    CO_CAJA = @ISCO_CAJA  
       and    AA_CAJA = @ISAA_CAJA  
       and    MM_CAJA = @ISMM_CAJA   
       and    TI_MOVI = @ISTI_MOVI  
       and    NU_COMP_CAJA = @ISNU_COMP_CAJA  
  
       If @VNNU_SECU > 0  
          Select @VNNU_SECU = @VNNU_SECU + 1   
       Else  
          Select @VNNU_SECU = 1           
  
       Select @VSCO_NUEV_CHAR = MAX(NU_COMP_CAJA)  
       From   TCMOVI_CAJA  
       Where  CO_EMPR = @ISCO_EMPR_DEST   
       And    CO_CAJA = @ISCO_CAJA_DEST   
       And    AA_CAJA = @VSAA_CAJA_DEST  
     And    MM_CAJA = @VSMM_CAJA_DEST   
       And    TI_MOVI = 'I'    
  
       If @VSCO_NUEV_CHAR is not null   
          Select @VNCO_NUEV_NUME = Convert(Int , @VSCO_NUEV_CHAR) + 1  
       Else  
          Select @VNCO_NUEV_NUME = 1  
  
       Select @VSCO_NUEV_CHAR = Convert(Char(8) , @VNCO_NUEV_NUME)  
  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 1  
          Select @VSCO_NUEV_CHAR = '0000000'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 2  
     Select @VSCO_NUEV_CHAR = '000000'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 3  
     Select @VSCO_NUEV_CHAR = '00000'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 4  
     Select @VSCO_NUEV_CHAR = '0000'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 5  
     Select @VSCO_NUEV_CHAR = '000'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 6  
     Select @VSCO_NUEV_CHAR = '00'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
       IF Datalength(Ltrim(Rtrim(@VSCO_NUEV_CHAR))) = 7  
     Select @VSCO_NUEV_CHAR = '0'+LTRIM(RTRIM(@VSCO_NUEV_CHAR))  
  
  
     Select @ISNU_COMP_DEST = @VSCO_NUEV_CHAR  

       INSERT INTO TCMOVI_CAJA (CO_EMPR, CO_CAJA, AA_CAJA, MM_CAJA, TI_MOVI, NU_COMP_CAJA,   
       FE_OPER, CO_TIPO_OPER, FA_TIPO_CAMB, IM_MOVI,  DE_OBSE, ST_CONT,  
       TI_SITU, CO_UNID_CONC, CO_USUA_CREA, CO_USUA_MODI, FE_USUA_CREA, FE_USUA_MODI)   
  
       Values ( @ISCO_EMPR_DEST, @ISCO_CAJA_DEST, @VSAA_CAJA_DEST, @VSMM_CAJA_DEST, 'I',   
       @ISNU_COMP_DEST, @IDFE_OPER, @ISCO_TIPO_DEST, @INFA_TIPO_DEST,   
       @INIM_CONV, @ISDE_OBSE, 'N',  'ACT', @ISCO_UNID_CNTB, @ISCO_USUA_MODI,@ISCO_USUA_MODI, GETDATE(), GETDATE())   


       If @@error <> 0  
          Begin  
              Rollback tran  
   Return  
          End    
	INSERT INTO TDMOVI_TRCA ( CO_EMPR, CO_CAJA, TI_MOVI, AA_CAJA, MM_CAJA,  
       NU_COMP_CAJA, NU_SECU, CO_EMPR_DEST, CO_CAJA_DEST, AA_CAJA_DEST, MM_CAJA_DEST, TI_MOVI_DEST,   
       NU_COMP_DEST, FE_OPER, CO_TIPO_OPER, FA_TIPO_CAMB, IM_MOVI, IM_CONV, CO_UNID_CONC, DE_OBSE,
	   CO_USUA_CREA, CO_USUA_MODI, FE_USUA_CREA, FE_USUA_MODI)  
       
	Values ( @ISCO_EMPR, @ISCO_CAJA, @ISTI_MOVI, @ISAA_CAJA, @ISMM_CAJA,  
       @ISNU_COMP_CAJA, @VNNU_SECU, @ISCO_EMPR_DEST, @ISCO_CAJA_DEST, @VSAA_CAJA_DEST,   
       @VSMM_CAJA_DEST, 'I', @ISNU_COMP_DEST, @IDFE_OPER, @ISCO_TIPO_DEST,   
       @INFA_TIPO_DEST, @INIM_MOVI, @INIM_CONV, @ISCO_UNID_CNTB, @ISDE_OBSE,
	   @ISCO_USUA_MODI, @ISCO_USUA_MODI,GETDATE(), GETDATE())

  
       If @@error <> 0  
          Begin  
            Rollback Tran  
     Return  
          End  
  
  
       Select  @VNUL_CORR_INGR = Convert (Int, @ISNU_COMP_DEST )  
       Select  @VNUL_CORR_INEG = Convert (Int, @ISNU_COMP_DEST )  
       Select  @VNIM_CONV = 0  
  
       If @ISTI_CORR_INEG = 'N'  
          Begin  
                Update TMSACA_MENS  
          Set   UL_CORR_INGR = @VNUL_CORR_INGR,   
   CO_USUA_MODI = @ISCO_USUA_MODI,   
                       FE_USUA_MODI = GETDATE()  
           Where  CO_EMPR = @ISCO_EMPR_DEST   
                 And    CO_CAJA = @ISCO_CAJA_DEST  
                 And AA_CAJA = @VSAA_CAJA_DEST  
                 And    MM_CAJA = @VSMM_CAJA_DEST  
  
             If @@error <> 0  
                    Begin  
                      Rollback tran                 Return  
                    End                 
  
         End  
  
       Else  
  
          Begin  
                Update  TMSACA_MENS  
         Set   UL_CORR_INEG = @VNUL_CORR_INEG,   
   CO_USUA_MODI = @ISCO_USUA_MODI,   
                       FE_USUA_MODI = GETDATE()  
          Where   CO_EMPR = @ISCO_EMPR_DEST   
                  And CO_CAJA = @ISCO_CAJA_DEST  
                  And AA_CAJA = @VSAA_CAJA_DEST  
                  And MM_CAJA = @VSMM_CAJA_DEST  
  
             If @@error <> 0  
         Begin  
                      Rollback tran  
               Return  
                    End          
  
         End  
  
      End  
    Else  
         Begin  
         Select @VNIM_CONV = Isnull(IM_CONV,0)  
             From   TDMOVI_TRCA   
           Where   CO_EMPR = @ISCO_EMPR  
                  And CO_CAJA = @ISCO_CAJA  
                  And AA_CAJA = @ISAA_CAJA  
                  And MM_CAJA = @ISMM_CAJA  
              And TI_MOVI = @ISTI_MOVI  
                  And NU_COMP_CAJA = @ISNU_COMP_CAJA  
                  And NU_SECU = @INNU_SECU  
  
             Update TDMOVI_TRCA   
             Set    FA_TIPO_CAMB =  @INFA_TIPO_DEST,  
                    CO_TIPO_OPER = @ISCO_TIPO_DEST,  
                    CO_UNID_CONC = @ISCO_UNID_CNTB,  
                    DE_OBSE = @ISDE_OBSE,  
                    IM_MOVI =  @INIM_MOVI,  
                    IM_CONV = @INIM_CONV,  
                    CO_USUA_MODI = @ISCO_USUA_MODI,  
                    FE_USUA_MODI = GETDATE()  
             Where  CO_EMPR = @ISCO_EMPR  
             And      CO_CAJA = @ISCO_CAJA  
             And      AA_CAJA = @ISAA_CAJA  
             And      MM_CAJA = @ISMM_CAJA  
             And      TI_MOVI = @ISTI_MOVI  
             And      NU_COMP_CAJA = @ISNU_COMP_CAJA  
             And      NU_SECU =  @INNU_SECU  
  
             If @@error <> 0  
                Begin  
                   Rollback tran  
       Return  
                End  
  
             Update TCMOVI_CAJA   
             Set    FA_TIPO_CAMB = @INFA_TIPO_DEST,   
                    CO_TIPO_OPER = @ISCO_TIPO_DEST,   
                    CO_UNID_CONC = @ISCO_UNID_CNTB,   
                    DE_OBSE = @ISDE_OBSE,   
                    IM_MOVI = @INIM_CONV,   
                    CO_USUA_MODI = @ISCO_USUA_MODI,   
                    FE_USUA_MODI = GETDATE()   
             Where  CO_EMPR = @ISCO_EMPR_DEST   
                  And CO_CAJA = @ISCO_CAJA_DEST  
                  And AA_CAJA = @VSAA_CAJA_DEST  
                  And MM_CAJA = @VSMM_CAJA_DEST  
                  And TI_MOVI = 'I'  
                  And NU_COMP_CAJA = @ISNU_COMP_DEST  
  
  
             If @@error <> 0  
                Begin  
                   Rollback tran  
       Return  
                End  
  
  
        End   
  
        Select @VNIM_MOVI_ANTE  = Isnull(IM_MOVI,0)   
        FROM   TCMOVI_CAJA  
        WHERE  CO_EMPR = @ISCO_EMPR   
        and    CO_CAJA = @ISCO_CAJA  
        and    AA_CAJA = @ISAA_CAJA  
        and    MM_CAJA = @ISMM_CAJA  
        and    TI_MOVI = @ISTI_MOVI  
        and    NU_COMP_CAJA = @ISNU_COMP_CAJA  
  
        Select @VNIM_TOTA = Isnull(Sum(Isnull(IM_MOVI,0)),0)  
        From TDMOVI_TRCA  
        Where CO_EMPR = @ISCO_EMPR  
        And   CO_CAJA = @ISCO_CAJA  
        And   AA_CAJA = @ISAA_CAJA  
        And   MM_CAJA = @ISMM_CAJA  
        And   TI_MOVI = @ISTI_MOVI  
        And   NU_COMP_CAJA = @ISNU_COMP_CAJA  
              
  
       Update TCMOVI_CAJA  
       Set   IM_MOVI = Round(@VNIM_TOTA,2),  
             CO_USUA_MODI = @ISCO_USUA_MODI, FE_USUA_MODI = getdate()           
       Where CO_EMPR = @ISCO_EMPR   
       And   CO_CAJA = @ISCO_CAJA  
       And   AA_CAJA = @ISAA_CAJA  
       And   MM_CAJA = @ISMM_CAJA  
       And   TI_MOVI = @ISTI_MOVI  
       And   NU_COMP_CAJA = @ISNU_COMP_CAJA  
  
       If @@error <> 0  
          Begin  
         Rollback tran  
 Return  
          End    
  
  
 COMMIT TRAN  
  
/* -------------------------- Fin -----------------------------------*/


GO
